package test.login;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import test.ConnectionManager;

public class UserController {

	private List<User> listUser = new ArrayList<User>();
	private PreparedStatement pstm = null;
	private ResultSet rs = null;

	public List<User> getUserList(UserSearch userSearch) {

		ConnectionManager cm = ConnectionManager.getInstance();

		// コネクション取得
		Connection con = cm.getConn();

		// SQLの生成
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT ");
		sql.append(" USER_ID, ");
		sql.append(" LOGIN_ID, ");
		sql.append(" USER_NAME, ");
		sql.append(" PASSWORD, ");
		sql.append(" ADMIN_FLG ");
		sql.append("FROM ");
		sql.append(" MUSER ");
		sql.append("WHERE ");
		sql.append("     LOGIN_ID LIKE ?");
		sql.append(" AND USER_NAME LIKE ?");
		sql.append(" AND ADMIN_FLG LIKE ?");
		sql.append("ORDER BY");
		sql.append(" USER_ID");

		try {
			// PreparedStatementの生成
			pstm = con.prepareStatement(sql.toString());

			// 引数で渡された文字列をバインド
			String getId = null;
			String getUser = null;
			String adminFlg = null;

			if (userSearch.getId() == null) {
				getId = "%";
			} else {
				getId = "%" + userSearch.getId() + "%";
			}
			if (userSearch.getUser() == null) {
				getUser = "%";
			} else {
				getUser = "%" + userSearch.getUser() + "%";
			}

			if ("all".equals(userSearch.getAdminFlg())) {
				adminFlg = "_";
			}
			if ("0".equals(userSearch.getAdminFlg())) {
				adminFlg = "0";
			}
			if ("1".equals(userSearch.getAdminFlg())) {
				adminFlg = "1";
			}

			pstm.setString(1, getId);
			pstm.setString(2, getUser);
			pstm.setString(3, adminFlg);

			// SQLを実行
			rs = pstm.executeQuery();

			User usertmp = new User();

			// 結果格納
			while (rs.next()) {
				usertmp = new User();
				usertmp.setUserId(Integer.parseInt(rs.getString("USER_ID")));
				usertmp.setLoginId(rs.getString("LOGIN_ID"));
				usertmp.setName(rs.getString("USER_NAME"));
				usertmp.setPassword(rs.getString("PASSWORD"));
				usertmp.setAdminFlg(rs.getString("ADMIN_FLG").charAt(0));
				listUser.add(usertmp);
			}

			con.commit();

		} catch (SQLException e) {
			System.err.println("SQLエラーです。");
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstm != null) {
					pstm.close();
				}
			} catch (SQLException e) {
				System.err.println("クローズに失敗");
			}
		}

		return listUser;
	}

	public User getUser(UserSearch userSearch) {

		ConnectionManager cm = ConnectionManager.getInstance();

		// コネクション取得
		Connection con = cm.getConn();

		// SQLの生成
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT ");
		sql.append(" USER_ID, ");
		sql.append(" LOGIN_ID, ");
		sql.append(" USER_NAME, ");
		sql.append(" PASSWORD, ");
		sql.append(" ADMIN_FLG ");
		sql.append("FROM ");
		sql.append(" MUSER ");
		sql.append("WHERE ");
		sql.append(" USER_ID = ? ");
		sql.append("ORDER BY");
		sql.append(" USER_ID");

		User usertmp = new User();

		try {
			// PreparedStatementの生成
			pstm = con.prepareStatement(sql.toString());

			// 引数で渡された文字列をバインド
			pstm.setString(1, userSearch.getId());

			// SQLを実行
			rs = pstm.executeQuery();

			// 結果格納
			usertmp = new User();

			rs.next();
			usertmp.setUserId(Integer.parseInt(rs.getString("USER_ID")));
			usertmp.setLoginId(rs.getString("LOGIN_ID"));
			usertmp.setName(rs.getString("USER_NAME"));
			usertmp.setPassword(rs.getString("PASSWORD"));
			usertmp.setAdminFlg(rs.getString("ADMIN_FLG").charAt(0));

			con.commit();

		} catch (SQLException e) {
			System.err.println("SQLエラーです。");
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstm != null) {
					pstm.close();
				}
			} catch (SQLException e) {
				System.err.println("クローズに失敗");
			}
		}

		return usertmp;
	}

	public void insertUser(User user) {

		ConnectionManager cm = ConnectionManager.getInstance();

		// コネクション取得
		Connection con = cm.getConn();

		// SQLの生成
		StringBuilder sql = new StringBuilder();

		sql.append("INSERT ");
		sql.append("INTO ");
		sql.append(" MUSER ");
		sql.append(" (USER_ID, ");
		sql.append(" LOGIN_ID, ");
		sql.append(" USER_NAME, ");
		sql.append(" PASSWORD, ");
		sql.append(" ADMIN_FLG) ");
		sql.append("VALUES ");
		sql.append(" ((SELECT MAX(USER_ID) + 1 USER_ID FROM MUSER), ");
		sql.append(" ?,");
		sql.append(" ?,");
		sql.append(" ?,");
		sql.append(" ?)");

		try {
			// PreparedStatementの生成
			pstm = con.prepareStatement(sql.toString());

			// 引数で渡された情報をバインド
			pstm.setString(1, user.getLoginId());
			pstm.setString(2, user.getName());
			pstm.setString(3, user.getPassword());
			pstm.setString(4, String.valueOf(user.getAdminFlg()));

			// SQLを実行
			rs = pstm.executeQuery();

			con.commit();

		} catch (SQLException e) {
			System.err.println("SQLエラーです。");
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstm != null) {
					pstm.close();
				}
			} catch (SQLException e) {
				System.err.println("クローズに失敗");
			}
		}

		return;
	}

	public void updateUser(User user) {

		ConnectionManager cm = ConnectionManager.getInstance();

		// コネクション取得
		Connection con = cm.getConn();

		// SQLの生成
		StringBuilder sql = new StringBuilder();

		sql.append("UPDATE ");
		sql.append(" MUSER ");
		sql.append("SET ");
		sql.append(" LOGIN_ID = ? , ");
		sql.append(" USER_NAME = ? , ");
		sql.append(" PASSWORD = ? , ");
		sql.append(" ADMIN_FLG = ? ");
		sql.append("WHERE ");
		sql.append(" USER_ID = ? ");

		try {
			// PreparedStatementの生成
			pstm = con.prepareStatement(sql.toString());

			// 引数で渡された情報をバインド
			pstm.setString(1, user.getLoginId());
			pstm.setString(2, user.getName());
			pstm.setString(3, user.getPassword());
			pstm.setString(4, String.valueOf(user.getAdminFlg()));
			pstm.setString(5, Integer.toString(user.getUserId()));

			// SQLを実行
			rs = pstm.executeQuery();

			con.commit();

		} catch (SQLException e) {
			System.err.println("SQLエラーです。");
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstm != null) {
					pstm.close();
				}
			} catch (SQLException e) {
				System.err.println("クローズに失敗");
			}
		}

		return;
	}

	public List<User> checkLogin(UserSearch userSearch) {

		ConnectionManager cm = ConnectionManager.getInstance();

		// コネクション取得
		Connection con = cm.getConn();

		// SQLの生成
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT ");
		sql.append(" USER_ID, ");
		sql.append(" LOGIN_ID, ");
		sql.append(" USER_NAME, ");
		sql.append(" PASSWORD, ");
		sql.append(" ADMIN_FLG ");
		sql.append("FROM ");
		sql.append(" MUSER ");
		sql.append("WHERE ");
		sql.append("     LOGIN_ID = ? ");
		sql.append(" AND PASSWORD = ? ");
		sql.append("ORDER BY");
		sql.append(" USER_ID");

		try {
			// PreparedStatementの生成
			pstm = con.prepareStatement(sql.toString());

			// 引数で渡された文字列をバインド
			String getId = userSearch.getId();
			String getUser = userSearch.getUser();

			pstm.setString(1, getId);
			pstm.setString(2, getUser);

			// SQLを実行
			rs = pstm.executeQuery();

			User usertmp = new User();

			// 結果格納
			while (rs.next()) {
				usertmp = new User();
				usertmp.setUserId(Integer.parseInt(rs.getString("USER_ID")));
				usertmp.setLoginId(rs.getString("LOGIN_ID"));
				usertmp.setName(rs.getString("USER_NAME"));
				usertmp.setPassword(rs.getString("PASSWORD"));
				usertmp.setAdminFlg(rs.getString("ADMIN_FLG").charAt(0));
				listUser.add(usertmp);
			}

			con.commit();

		} catch (SQLException e) {
			System.err.println("SQLエラーです。");
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstm != null) {
					pstm.close();
				}
			} catch (SQLException e) {
				System.err.println("クローズに失敗");
			}
		}

		return listUser;
	}
}
